package inet.app.controller.database;

import inet.app.model.object.SubKq_Item;
import inet.app.model.object.SubSv_Item;
import inet.app.model.object.SubTk_Item;
import inet.app.model.object.Veso_Item;
import inet.app.model.util.SmartLog;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseHandler extends SQLiteOpenHelper {

	private SQLiteDatabase sqLiteDatabase;

	// instance of this class
	private String TB_SUB_SV = "SUB_SV";
	private String TB_SUB_KQ = "SUB_KQ";
	private String TB_SUB_TKXS = "SUB_TKXS";
	private String TB_VE_SO = "VE_SO";

	private String create_sub_sv = "CREATE TABLE SUB_SV (ID varchar(10) PRIMARY KEY,CODE TEXT, WEEKOFYEAR TEXT)";
	private String create_sub_kq = "CREATE TABLE SUB_KQ (ID varchar(10) PRIMARY KEY,CODE TEXT, DATE TEXT)";
	private String create_ve_so = "CREATE TABLE VE_SO (ID INTEGER PRIMARY KEY AUTOINCREMENT,LISTNUMBER TEXT, CODE TEXT, DATE TEXT,KETQUA TEXT,NAME TEXT)";
	private String create_sub_tk = "CREATE TABLE SUB_TKXS (ID varchar(10) PRIMARY KEY,CODE TEXT, WEEKOFYEAR TEXT)";

	// create table kqgiai region
	public String create_table_kqgiai_region = "CREATE TABLE "
			+ DBConfig.TABLE_KQGIAI_REGION + "( " + DBConfig.COL_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
			+ DBConfig.COL_KQGIAI_REGION_REGION + " TEXT,"
			+ DBConfig.COL_KQGIAI_REGION_DATE + " TEXT);";

	// create table kqgiai xs
	public String create_table_kqgiai_xs = "CREATE TABLE "
			+ DBConfig.TABLE_KQGIAI_XS + "( " + DBConfig.COL_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
			+ DBConfig.COL_KQGIAI_XS_CODE + " TEXT, "
			+ DBConfig.COL_KQGIAI_XS_NAME + " TEXT, "
			+ DBConfig.COL_KQGIAI_XS_DATE + " TEXT, "
			+ DBConfig.COL_KQGIAI_XS_REGIONID + " INTEGER, "
			+ DBConfig.COL_KQGIAI_XS_GIAIDB + " TEXT, "
			+ DBConfig.COL_KQGIAI_XS_GIAI1 + " TEXT, "
			+ DBConfig.COL_KQGIAI_XS_GIAI2 + " TEXT, "
			+ DBConfig.COL_KQGIAI_XS_GIAI3 + " TEXT, "
			+ DBConfig.COL_KQGIAI_XS_GIAI4 + " TEXT, "
			+ DBConfig.COL_KQGIAI_XS_GIAI5 + " TEXT, "
			+ DBConfig.COL_KQGIAI_XS_GIAI6 + " TEXT, "
			+ DBConfig.COL_KQGIAI_XS_GIAI7 + " TEXT, "
			+ DBConfig.COL_KQGIAI_XS_GIAI8 + " TEXT);";

	// create table kqgiai xs dien toan
	public String create_table_kqgiai_xsdt = "CREATE TABLE "
			+ DBConfig.TABLE_KQGIAI_XSDT + "( " + DBConfig.COL_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
			+ DBConfig.COL_KQGIAI_XSDT_DATE + " TEXT, "
			+ DBConfig.COL_KQGIAI_XSDT_DT123 + " TEXT, "
			+ DBConfig.COL_KQGIAI_XSDT_DT6x36 + " TEXT, "
			+ DBConfig.COL_KQGIAI_XSDT_TT + " TEXT);";

	// create table somo
	public String create_table_somo = "CREATE TABLE " + DBConfig.TABLE_SOMO
			+ "( " + DBConfig.COL_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
			+ DBConfig.COL_SOMO_NAME + " TEXT, " + DBConfig.COL_SOMO_NAMEVN
			+ " TEXT, " + DBConfig.COL_SOMO_NUMBERS + " TEXT);";

	// create table thongke nhanh
	public String create_table_thongkenhanh = "CREATE TABLE "
			+ DBConfig.TABLE_TK_NHANH + "( " + DBConfig.COL_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
			+ DBConfig.COL_TK_NHANH_CODE + " TEXT, "
			+ DBConfig.COL_TK_NHANH_NAME + " TEXT, "
			+ DBConfig.COL_TK_NHANH_NUMBERDATE + " TEXT, "
			+ DBConfig.COL_TK_NHANH_NUMBERCOUPLE + " TEXT, "
			+ DBConfig.COL_TK_NHANH_TKCS + " TEXT, "
			+ DBConfig.COL_TK_NHANH_MOST + " TEXT, "
			+ DBConfig.COL_TK_NHANH_LEAST + " TEXT, "
			+ DBConfig.COL_TK_NHANH_NUMBER + " TEXT, "
			+ DBConfig.COL_TK_NHANH_DATE + " TEXT, "
			+ DBConfig.COL_TK_NHANH_CHUARA + " TEXT, "
			+ DBConfig.COL_TK_NHANH_CUCDAI + " TEXT);";
	
	//create table tinh soi cau
	public String create_table_tinhsc = "CREATE TABLE "
			+ DBConfig.TABLE_TINH_SC + "(" + DBConfig.COL_ID
			+ " INTEGER PRIMARY KEY NOT NULL, "
			+ DBConfig.COL_TINH_SC_CODE + " TEXT,"
			+ DBConfig.COL_TINH_SC_NAME + " TEXT);";

	private String drop_sub_sv = "DROP TABLE IF EXISTS SUB_SV";
	private String drop_sub_kq = "DROP TABLE IF EXISTS SUB_KQ";
	private String drop_sub_tk = "DROP TABLE IF EXISTS SUB_TKXS";
	private String drop_ve_so = "DROP TABLE IF EXISTS VE_SO";
	private String drop_table_kqgiai_xs = "DROP TABLE IF EXISTS "
			+ DBConfig.TABLE_KQGIAI_XS;
	private String drop_table_kqgiai_xsdt = "DROP TABLE IF EXISTS "
			+ DBConfig.TABLE_KQGIAI_XSDT;
	private String drop_table_kqgiai_region = "DROP TABLE IF EXISTS "
			+ DBConfig.TABLE_KQGIAI_REGION;
	private String drop_table_somo = "DROP TABLE IF EXISTS "
			+ DBConfig.TABLE_SOMO;
	private String drop_table_thongkenhanh = "DROP TABLE IF EXISTS "
			+ DBConfig.TABLE_TK_NHANH;
	private String drop_table_tinhsc = "DROP TABLE IF EXISTS "
			+ DBConfig.TABLE_TINH_SC;

	public DataBaseHandler(Context context) {
		super(context, DBConfig.getDatabaseName(), null, DBConfig
				.getDBVersion());
	}

	private boolean checkDataBase() {
		SQLiteDatabase checkDB = null;
		try {
			checkDB = SQLiteDatabase.openDatabase(DBConfig.getDBFullPath(),
					null, SQLiteDatabase.OPEN_READONLY);
		} catch (SQLiteException e) {
			return false;
		}
		if (checkDB != null) {
			checkDB.close();
		}
		return checkDB != null ? true : false;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		boolean dbExist = checkDataBase();
		if (!dbExist) {
			createTable(db);
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
		droptTable(db);
		createTable(db);
	}

	/***
	 * 
	 * @return
	 */
	public boolean openSession() {
		try {
			sqLiteDatabase = getWritableDatabase();
			return true;
		} catch (RuntimeException runtimeException) {
			return false;
		}
	}

	/**
	 * 
	 * @param query
	 * @return
	 */
	public int makeQuery(String query) {
		sqLiteDatabase.beginTransaction();
		try {
			sqLiteDatabase.execSQL(query);
			sqLiteDatabase.setTransactionSuccessful();
			return 1;
		} catch (SQLException sqlException) {
			// sqlException.printStackTrace();
			SmartLog.i(sqlException.toString());
			return 0;
		} finally {
			sqLiteDatabase.endTransaction();
		}

	}

	/**
	 * 
	 * @param query
	 * @return
	 */
	public Cursor getCursor(String query) {
		Cursor cursor = sqLiteDatabase.rawQuery(query, null);
		return cursor;
	}

	/**
	 * closes the database
	 */
	public void closeSession() {
		try {
			sqLiteDatabase.close();
		} catch (RuntimeException runtimeException) {
			runtimeException.printStackTrace();
		}
	}

	/**
	 * insert a row to desire table
	 * 
	 * @param table
	 *            the table to insert the row into optional; may be null. SQL
	 *            doesn't allow inserting a completely empty row without naming
	 *            at least one column name. If your provided values is empty, no
	 *            column names are known
	 * @param nullColumnHack
	 *            and an empty row can't be inserted. If not set to null, the
	 *            nullColumnHack parameter provides the name of nullable column
	 *            name to explicitly insert a NULL into in the case where your
	 *            values is empty.
	 * @param values
	 *            this map contains the initial column values for the row. The
	 *            keys should be the column names and the values the column
	 *            values
	 * @return the row ID of the newly inserted row, or -1 if an error occurred
	 */
	public long insert(String table, String nullColumnHack, ContentValues values) {
		sqLiteDatabase.beginTransaction();
		long insertId = -1;
		try {
			insertId = sqLiteDatabase.insert(table, nullColumnHack, values);
			sqLiteDatabase.setTransactionSuccessful();
		} catch (Exception e) {
			insertId = -1;
		} finally {
			sqLiteDatabase.endTransaction();
		}
		return insertId;
	}

	/**
	 * insert a row collection to desire table
	 * 
	 * @param table
	 *            the table to insert the row into optional; may be null. SQL
	 *            doesn't allow inserting a completely empty row without naming
	 *            at least one column name. If your provided values is empty, no
	 *            column names are known
	 * @param nullColumnHack
	 *            and an empty row can't be inserted. If not set to null, the
	 *            nullColumnHack parameter provides the name of nullable column
	 *            name to explicitly insert a NULL into in the case where your
	 *            values is empty.
	 * @param values
	 *            row collection. The keys should be the column names and the
	 *            values the column values
	 * @return the row ID of the newly inserted row, or -1 if an error occurred
	 */
	public long insert(String table, String nullColumnHack,
			List<ContentValues> values) {
		sqLiteDatabase.beginTransaction();
		long insertId = -1;
		try {
			for (ContentValues value : values) {
				insertId = sqLiteDatabase.insert(table, nullColumnHack, value);
			}
			sqLiteDatabase.setTransactionSuccessful();
		} catch (Exception e) {
			insertId = -1;
		} finally {
			sqLiteDatabase.endTransaction();
		}
		return insertId;
	}

	/**
	 * Convenience method for deleting rows in the database.x
	 * 
	 * @param table
	 * @param whereClause
	 *            the optional WHERE clause to apply when deleting. Passing null
	 *            will delete all rows.
	 * @param whereArgs
	 * @return the number of rows affected if a whereClause is passed in, 0
	 *         otherwise. To remove all rows and get a count pass "1" as the
	 *         whereClause.
	 */
	public int delete(String table, String whereClause, String[] whereArgs) {
		sqLiteDatabase.beginTransaction();
		int res = -1;
		try {
			res = sqLiteDatabase.delete(table, whereClause, whereArgs);
			sqLiteDatabase.setTransactionSuccessful();
		} catch (Exception e) {
			res = -1;
		} finally {
			sqLiteDatabase.endTransaction();
		}
		return res;
	}

	/**
	 * Query the given table, returning a Cursor over the result set.
	 * 
	 * @param table
	 * @param columns
	 *            A list of which columns to return. Passing null will return
	 *            all columns, which is discouraged to prevent reading data from
	 *            storage that isn't going to be used.
	 * @param selection
	 *            A filter declaring which rows to return, formatted as an SQL
	 *            WHERE clause (excluding the WHERE itself). Passing null will
	 *            return all rows for the given table.
	 * @param selectionArgs
	 *            You may include ?s in selection, which will be replaced by the
	 *            values from selectionArgs, in order that they appear in the
	 *            selection. The values will be bound as Strings.
	 * @param groupBy
	 *            A filter declaring how to group rows, formatted as an SQL
	 *            GROUP BY clause (excluding the GROUP BY itself). Passing null
	 *            will cause the rows to not be grouped.
	 * @param having
	 *            A filter declare which row groups to include in the cursor, if
	 *            row grouping is being used, formatted as an SQL HAVING clause
	 *            (excluding the HAVING itself). Passing null will cause all row
	 *            groups to be included, and is required when row grouping is
	 *            not being used.
	 * @param orderBy
	 *            How to order the rows, formatted as an SQL ORDER BY clause
	 *            (excluding the ORDER BY itself). Passing null will use the
	 *            default sort order, which may be unordered.
	 * @return A Cursor object, which is positioned before the first entry. Note
	 *         that Cursors are not synchronized, see the documentation for more
	 *         details.
	 */
	public Cursor query(String table, String[] columns, String selection,
			String[] selectionArgs, String groupBy, String having,
			String orderBy) {

		sqLiteDatabase.beginTransaction();
		Cursor res = null;
		try {
			res = sqLiteDatabase.query(table, columns, selection,
					selectionArgs, groupBy, having, orderBy);
			sqLiteDatabase.setTransactionSuccessful();
		} catch (Exception e) {
			res = null;
		} finally {
			sqLiteDatabase.endTransaction();
		}
		return res;
	}

	/**
	 * Query the given table, returning a Cursor over the result set.
	 * 
	 * @param sql
	 *            raw sql statement
	 * @param selectionArgs
	 * @return
	 */
	public Cursor rawQuery(String sql, String[] selectionArgs) {
		sqLiteDatabase.beginTransaction();
		Cursor res = null;
		try {
			res = sqLiteDatabase.rawQuery(sql, selectionArgs);
			sqLiteDatabase.setTransactionSuccessful();
		} catch (Exception e) {
			res = null;
		} finally {
			sqLiteDatabase.endTransaction();
		}
		return res;
	}

	/**
	 * Update a row in desired table
	 * 
	 * @param table
	 * @param values
	 * @param whereClause
	 * @param whereArgs
	 * @return
	 */
	public int update(String table, ContentValues values, String whereClause,
			String[] whereArgs) {
		sqLiteDatabase.beginTransaction();
		int res = -1;
		try {
			res = sqLiteDatabase.update(table, values, whereClause, whereArgs);
			sqLiteDatabase.setTransactionSuccessful();
		} catch (Exception e) {
			res = 0;
		} finally {
			sqLiteDatabase.endTransaction();
		}
		return res;
	}

	public SubSv_Item getSubSv(String code) {
		SQLiteDatabase db = this.getReadableDatabase();
		String query = "SELECT * FROM " + TB_SUB_SV + " WHERE CODE = '" + code
				+ "'";
		Cursor cursor = db.rawQuery(query, null);
		SubSv_Item item = null;
		while (cursor != null && cursor.moveToNext()) {
			item = new SubSv_Item(cursor.getString(1), cursor.getString(2));
		}
		db.close();
		return item;
	}

	public int updateSubSv(SubSv_Item item) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("CODE", item.getCode());
		values.put("WEEKOFYEAR", item.getWeekofyear());
		return db.update(TB_SUB_SV, values, "WEEKOFYEAR" + " = ?",
				new String[] { String.valueOf(item.getWeekofyear()) });
	}

	public void insertSubSv(SubSv_Item item_sub) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values = new ContentValues();
		values.put("CODE", item_sub.getCode());
		values.put("WEEKOFYEAR", item_sub.getWeekofyear());
		db.insert(TB_SUB_SV, null, values);
		db.close(); // Closing database connection
	}

	/*----------------------------------------------------------------------------------*/
	public SubKq_Item getSubKq(String code) {
		SQLiteDatabase db = this.getReadableDatabase();
		String query = "SELECT * FROM " + TB_SUB_KQ + " WHERE CODE = '" + code
				+ "'";
		Cursor cursor = db.rawQuery(query, null);
		SubKq_Item item = null;
		while (cursor != null && cursor.moveToNext()) {
			item = new SubKq_Item(cursor.getString(1), cursor.getString(2));
		}
		db.close();
		return item;
	}

	public int updateSubKq(SubKq_Item item) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues contentValues = new ContentValues();

		contentValues.put("CODE", item.getCode());
		contentValues.put("DATE", item.getDate());

		return db.update(TB_SUB_KQ, contentValues, "DATE" + " = ?",
				new String[] { String.valueOf(item.getDate()) });
	}

	public void insertSubKq(SubKq_Item item) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values = new ContentValues();
		values.put("CODE", item.getCode());
		values.put("DATE", item.getDate());
		db.insert(TB_SUB_KQ, null, values);
		db.close();
	}

	// Deleting single Xssync_mb
	public void deleteSubKq(String id) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TB_VE_SO, "CODE" + "=?", new String[] { String.valueOf(id) });
		System.out.println("XÓA THÀNH CONG");
		db.close();
	}

	/*----------------------------------------------------------------------------------*/
	public SubTk_Item getSubTk(String code) {
		SQLiteDatabase db = this.getReadableDatabase();
		String query = "SELECT * FROM " + TB_SUB_TKXS + " WHERE CODE = '"
				+ code + "'";
		Cursor cursor = db.rawQuery(query, null);
		SubTk_Item item = null;
		while (cursor != null && cursor.moveToNext()) {
			item = new SubTk_Item(cursor.getString(1), cursor.getString(2));
		}
		db.close();
		return item;
	}

	public int updateSubTk(SubTk_Item item) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("CODE", item.getCode());
		values.put("WEEKOFYEAR", item.getWeekofyear());
		return db.update(TB_SUB_TKXS, values, "WEEKOFYEAR" + " = ?",
				new String[] { String.valueOf(item.getWeekofyear()) });
	}

	public void insertSubTk(SubTk_Item item_sub) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values = new ContentValues();
		values.put("CODE", item_sub.getCode());
		values.put("WEEKOFYEAR", item_sub.getWeekofyear());
		db.insert(TB_SUB_TKXS, null, values);
		db.close();
	}

	/*----------------------------------------------------------------------------------*/
	public ArrayList<Veso_Item> getVeSo() {
		ArrayList<Veso_Item> list = new ArrayList<Veso_Item>();
		Veso_Item item = null;
		SQLiteDatabase db = this.getReadableDatabase();
		String query = "SELECT * FROM " + TB_VE_SO;
		Cursor cursor = db.rawQuery(query, null);
		while (cursor != null && cursor.moveToNext()) {
			item = new Veso_Item(cursor.getString(1), cursor.getString(2),
					cursor.getString(3), cursor.getString(4),
					cursor.getString(5));
			list.add(item);
		}
		db.close();
		return list;
	}

	public int updateVeSo(Veso_Item item) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("LISTNUMBER", item.getListNumber());
		values.put("CODE", item.getCode());
		values.put("DATE", item.getDate());
		values.put("KETQUA", item.getKetqua());
		values.put("NAME", item.getName());
		return db.update(TB_VE_SO, values, "LISTNUMBER" + " = ?",
				new String[] { String.valueOf(item.getListNumber()) });
	}

	public void insertVeSo(Veso_Item item_sub) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values = new ContentValues();
		values.put("LISTNUMBER", item_sub.getListNumber());
		values.put("CODE", item_sub.getCode());
		values.put("DATE", item_sub.getDate());
		values.put("KETQUA", item_sub.getKetqua());
		values.put("NAME", item_sub.getName());
		db.insert(TB_VE_SO, null, values);
		db.close();
	}

	// Deleting single Xssync_mb
	public void deleteVeSo(int id) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TB_VE_SO, "LISTNUMBER" + "=?",
				new String[] { String.valueOf(id) });
		System.out.println("XÓA THÀNH CONG");
		db.close();
	}

	/*----------------------------------------------------------------------------------*/

	public void createTable(SQLiteDatabase db) {

		db.execSQL(create_sub_sv);
		db.execSQL(create_sub_kq);
		db.execSQL(create_sub_tk);
		db.execSQL(create_ve_so);
		db.execSQL(create_table_kqgiai_xs);
		db.execSQL(create_table_kqgiai_xsdt);
		db.execSQL(create_table_kqgiai_region);
		db.execSQL(create_table_somo);
		db.execSQL(create_table_thongkenhanh);
		db.execSQL(create_table_tinhsc);
	}

	public void droptTable(SQLiteDatabase db) {

		db.execSQL(drop_sub_sv);
		db.execSQL(drop_sub_kq);
		db.execSQL(drop_sub_tk);
		db.execSQL(drop_ve_so);
		db.execSQL(drop_table_kqgiai_xs);
		db.execSQL(drop_table_kqgiai_xsdt);
		db.execSQL(drop_table_kqgiai_region);
		db.execSQL(drop_table_somo);
		db.execSQL(drop_table_thongkenhanh);
		db.execSQL(drop_table_tinhsc);
	}

	/*----------------------------------------------------------------------------------*/
	public int getCount() {
		SQLiteDatabase db = this.getReadableDatabase();
		String query = "SELECT * FROM " + TB_SUB_SV;
		Cursor cursor = db.rawQuery(query, null);
		int size = cursor.getCount();
		db.close();
		return size;
	}

	public int getCountKq() {
		SQLiteDatabase db = this.getReadableDatabase();
		String query = "SELECT * FROM " + TB_SUB_KQ;
		Cursor cursor = db.rawQuery(query, null);
		int size = cursor.getCount();
		db.close();
		return size;
	}

	public int getCountVs() {
		SQLiteDatabase db = this.getReadableDatabase();
		String query = "SELECT * FROM " + TB_VE_SO;
		Cursor cursor = db.rawQuery(query, null);
		int size = cursor.getCount();
		db.close();
		return size;
	}

	public int deleteAll(String table) {
		SQLiteDatabase db = this.getWritableDatabase();
		return db.delete(table, null, null);
	}
}
